package org.xiaojl.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class ExcelReaderUtil {
	private static final Logger logger = LoggerFactory.getLogger(ExcelReaderUtil.class);  
  
    private ExcelReaderUtil() {  
    }  
      
    /** 
     * 读取Excel表格表头的内容 【读取第一行数据】
     */  
    public static String[] readExcelTitle(String filepath) throws Exception{  
    	if(filepath==null || "".equals(filepath.trim())){  
    		throw new Exception("文件路径不能为空！");
        }
    	try {
            InputStream is = new FileInputStream(filepath);  
            Workbook wb = WorkbookFactory.create(is);
            if(wb==null){  
                throw new Exception("Workbook对象为空！");  
            }  
            Sheet sheet = wb.getSheetAt(0);  
            Row row = sheet.getRow(0);  
            // 标题总列数  
            int colNum = row.getPhysicalNumberOfCells();  
            System.out.println("标题总列数colNum:" + colNum);  
            String[] title = new String[colNum];  
            for (int i = 0; i < colNum; i++) {  
                // title[i] = getStringCellValue(row.getCell((short) i));  
                title[i] = row.getCell(i).getCellFormula();  
            }  
            return title;
            
        } catch (FileNotFoundException e) {  
            logger.error("要读取的EXCEL文件不存在！", e);  
        } catch (Exception e) {  
        	logger.error("读取的EXCEL文件出现异常：", e);  
		}  
    	
    	return null;
    }  
  
    /** 
     * 读取Excel数据内容 【从第二行开始读取数据】
     */  
    public static Map<Integer, Map<Integer,Object>> readExcelContent(String filepath) throws Exception{
    	if(filepath==null || "".equals(filepath.trim())){  
    		throw new Exception("文件路径不能为空！");
        }
    	try {
            InputStream is = new FileInputStream(filepath);  
            Workbook wb = WorkbookFactory.create(is);
            if(wb==null){  
                throw new Exception("Workbook对象为空！");  
            }  
	        Map<Integer, Map<Integer,Object>> content = new HashMap<Integer, Map<Integer,Object>>();  
	          
	        Sheet sheet = wb.getSheetAt(0);  
	        // 得到总行数  
	        int rowNum = sheet.getLastRowNum();  
	        Row row = sheet.getRow(0);  
	        int colNum = row.getPhysicalNumberOfCells();  
	        // 正文内容应该从第二行开始,第一行为表头的标题  
	        for (int i = 1; i <= rowNum; i++) {  
	            row = sheet.getRow(i);  
	            int j = 0;  
	            Map<Integer,Object> cellValue = new HashMap<Integer, Object>();  
	            while (j < colNum) {  
	                Object obj = getCellFormatValue(row.getCell(j));  
	                cellValue.put(j, obj);  
	                j++;  
	            }  
	            content.put(i, cellValue);  
	        }  
	        return content;
    	} catch (FileNotFoundException e) {  
            logger.error("要读取的EXCEL文件不存在！", e);  
        } catch (Exception e) {  
        	logger.error("读取的EXCEL文件出现异常：", e);  
		}  
    	
    	return null;
    }  
  
    /** 
     * 根据Cell类型设置数据 
     */  
    private static Object getCellFormatValue(Cell cell) {  
        Object cellvalue = "";  
        if (cell != null) {  
            // 判断当前Cell的Type  
            switch (cell.getCellType()) {  
            case Cell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC  
            case Cell.CELL_TYPE_FORMULA: {  
                // 判断当前的cell是否为Date  
                if (DateUtil.isCellDateFormatted(cell)) {  
                    // 如果是Date类型则，转化为Data格式  
                    // data格式是带时分秒的：2013-7-10 0:00:00  
                    // cellvalue = cell.getDateCellValue().toLocaleString();  
                    // data格式是不带带时分秒的：2013-7-10  
                    Date date = cell.getDateCellValue();  
                    cellvalue = date;  
                } else {// 如果是纯数字  
                    // 取得当前Cell的数值  
                    cellvalue = String.valueOf(cell.getNumericCellValue());  
                }  
                break;  
            }  
            case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING  
                // 取得当前的Cell字符串  
                cellvalue = cell.getRichStringCellValue().getString();  
                break;  
            default:// 默认的Cell值  
                cellvalue = "";  
            }  
        } else {  
            cellvalue = "";  
        }  
        return cellvalue;  
    }  
  
    public static void main(String[] args) {  
        try {  
            String filepath = "D://test1.xlsx";  
            
            ExcelReaderUtil.readExcelTitle(filepath);
            
            // 对读取Excel表格内容测试  
            Map<Integer, Map<Integer,Object>> map = ExcelReaderUtil.readExcelContent(filepath);  
            System.out.println("获得Excel表格的内容:");  
            for (int i = 1; i <= map.size(); i++) {  
                System.out.println(map.get(i));  
                
                
            }  
        } catch (FileNotFoundException e) {  
            System.out.println("未找到指定路径的文件!");  
            e.printStackTrace();  
        }catch (Exception e) {  
            e.printStackTrace();  
        }  
    }  
}
